package com.surekam.core.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Excel2010Operation {

	public static void main(String[] args) throws Exception {

		// File file = new File("src\\excelResourceFile\\privilege.xlsx");
		File file = new File("农信门户系统评审.xlsx");

		Excel2010Operation excel = new Excel2010Operation();

		List<List<String[]>> workBook = excel.getData(file, "all", 0);
		for (List<String[]> sheet : workBook) {
			for (String[] row : sheet) {
				for (int col = 0; col < row.length; col++) {
					System.out.print(row[col] + "\t");
				}
				System.out.println();
			}

			System.out.println();
			System.out.println("……………… one sheet read end …………………………………");
			System.out.println();

		}
	}

	// ignoreRows 表示读取某个表时，忽略的行数。
	public List<List<String[]>> getData(File file, String privilegeType, int ignoreRows) throws FileNotFoundException,
			IOException {

		List<List<String[]>> sheets = new ArrayList<List<String[]>>();// 一个list存储一个工作簿

		BufferedInputStream in = new BufferedInputStream(new FileInputStream(

		file));

		XSSFWorkbook workbook = new XSSFWorkbook(in);

		// start read a sheet
		sheets = this.getWorkBook(workbook, privilegeType, ignoreRows);
		// end read a sheet

		in.close();
		return sheets;

	}

	// start 获取excle 一个 工作簿(workBook)的记录
	public List<List<String[]>> getWorkBook(XSSFWorkbook workbook, String privilegeType, int ignoreRows) {

		// 一个list存储一个工作簿
		List<List<String[]>> sheets = new ArrayList<List<String[]>>();

		for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {

			List<String[]> result = new ArrayList<String[]>();// 一个list存储一个sheet的所有行

			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
			String sheetName = sheet.getSheetName(); // 获取sheet的名字

			if ("ALL".equalsIgnoreCase(privilegeType)) {
			} else if (!sheetName.equalsIgnoreCase(privilegeType)) {
				continue;
			}
			// System.out.println(sheetName);

			// 获取表格，ignoreRows为忽略的行数；

			result = getExcelSheet(sheet, ignoreRows);
			sheets.add(result);
		}

		return sheets;
	}

	// end 获取excle 一个 工作簿(workBook)的记录

	// start 获取excle 一个表(sheet)的记录
	public List<String[]> getExcelSheet(XSSFSheet xssfSheet, int ignoreRows) {
		XSSFSheet sheet = xssfSheet;

		// 一个list存储一个workBook的所有行
		List<String[]> result = new ArrayList<String[]>();

		for (int rowIndex = ignoreRows; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
			XSSFRow row = sheet.getRow(rowIndex);
			if (row == null) {

				continue;
			}

			String values[] = getExcelRole(row);

			result.add(values);
		}

		return result;
	}

	// end 获取excle 一个表(sheet)的记录

	// start 获取excle 一行的记录
	public String[] getExcelRole(XSSFRow xSSFRow) {

		XSSFRow row = xSSFRow;
		int rowSize = 0;
		XSSFCell cell = null;

		if (row == null) {
			System.out.println("空空");
			return null;

		}

		int tempRowSize = row.getLastCellNum() + 1;// 获取列数

		if (tempRowSize > rowSize) {

			rowSize = tempRowSize;// 获取列数

		}

		String[] values = new String[rowSize];// values store a row 's all
		// column value;
		Arrays.fill(values, "");

		boolean hasValue = false;

		// start all column for a row

		for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

			String value = "";

			cell = row.getCell(columnIndex);

			if (cell != null) {

				// 注意：一定要设成这个，否则可能会出现乱码

				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);

				switch (cell.getCellType()) {

				case XSSFCell.CELL_TYPE_STRING:

					value = cell.getStringCellValue();

					break;

				case XSSFCell.CELL_TYPE_NUMERIC:

					if (HSSFDateUtil.isCellDateFormatted(cell)) {

						Date date = cell.getDateCellValue();

						if (date != null) {

							value = new SimpleDateFormat("yyyy-MM-dd")

							.format(date);

						} else {

							value = "";

						}

					} else {

						value = new DecimalFormat("0").format(cell

						.getNumericCellValue());

					}

					break;

				case XSSFCell.CELL_TYPE_FORMULA:

					// 导入时如果为公式生成的数据则无值

					if (!cell.getStringCellValue().equals("")) {

						value = cell.getStringCellValue();

					} else {

						value = cell.getNumericCellValue() + "";

					}

					break;

				case XSSFCell.CELL_TYPE_BLANK:

					break;

				case XSSFCell.CELL_TYPE_ERROR:

					value = "";

					break;

				case XSSFCell.CELL_TYPE_BOOLEAN:

					value = (cell.getBooleanCellValue() == true ? "Y"

					: "N");

					break;

				default:

					value = "";

				}

			}

			if (columnIndex == 0 && value.trim().equals("")) {

				break;

			}

			values[columnIndex] = this.rightTrim(value);

			hasValue = true;

		}

		return values;
	}

	// end 获取excle 一行的记录

	public String rightTrim(String str) {

		if (str == null) {

			return "";

		}

		int length = str.length();

		for (int i = length - 1; i >= 0; i--) {

			if (str.charAt(i) != 0x20) {

				break;

			}

			length--;

		}

		return str.substring(0, length);

	}

}
